package shop.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import shop.bean.OfferBean;
import shop.util.ConnectionManager;

public class OffersDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;  

	public static Collection<OfferBean> getOffers() {		
		Collection<OfferBean> offerBeanList = new ArrayList<OfferBean>();    // a list    
		//preparing some objects for connection 
		Statement stmt = null; 
		
		String searchQuery = "SELECT ITEM_ID, NAME, DESCRIPTION, PRICE, COUNTRY,"
				+ "REGION, QUANTITY from items";
		System.out.println("Search Query:" + searchQuery);
		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        
			while(rs.next()){
				OfferBean ob = new OfferBean();
				ob.setId(rs.getInt("ITEM_ID"));
                ob.setName(rs.getString("NAME"));
                ob.setDescription(rs.getString("DESCRIPTION"));
                ob.setPrice(rs.getDouble("PRICE"));
                ob.setCountry(rs.getInt("COUNTRY"));
                ob.setRegion(rs.getString("REGION"));
                ob.setQuantity(rs.getInt("QUANTITY"));
                offerBeanList.add(ob);  
			}
			
		    // analyze the result set

		} 

		catch (Exception ex) 
		{
			System.out.println("No Database access: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}

		//return offerBeanList;
		return offerBeanList;

	}	
	
	
	
	public static OfferBean getOffer(int id){

		//preparing some objects for connection 
		Statement stmt = null; 
		
		String searchQuery = "SELECT ITEM_ID, NAME, DESCRIPTION, PRICE, COUNTRY,"
				+ "REGION, QUANTITY from items WHERE item_id="+ id;

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        

		    // analyze the result set

            while(rs.next()){
                
                OfferBean ob = new OfferBean();
                
                ob.setId(rs.getInt("ITEM_ID"));
                ob.setName(rs.getString("NAME"));
                ob.setDescription(rs.getString("DESCRIPTION"));
                ob.setPrice(rs.getDouble("PRICE"));
                ob.setCountry(rs.getInt("COUNTRY"));
                ob.setRegion(rs.getString("REGION"));
                ob.setQuantity(rs.getInt("QUANTITY"));
                
                return ob;
            }
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return null;
	}
	
	public static int getQuantity(int id){
		//preparing some objects for connection 
		Statement stmt = null; 
		
		String searchQuery = "SELECT quantity from items WHERE item_id="+ id;
		int quantity = 0;
		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        

		    // analyze the result set
			
            if(rs.next()){
                quantity = rs.getInt("quantity");
                System.out.println(quantity);
            }else {
            	System.out.println("No quantity found");
            }
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return quantity;
	}
	
	
	public static void saveOffer(OfferBean o){

		//preparing some objects for connection 
		Statement stmt = null; 

		String query = "UPDATE items SET " +
						"ITEM_ID="+ o.getId() +", " +
						"NAME='"+ o.getName() +"'," +
						"PRICE='"+ o.getPrice() +"'," +
						"COUNTRY='"+ o.getCountry() +"'," +
						"REGION='"+ o.getRegion() +"'," +
						"QUANTITY='"+ o.getQuantity() +"'," +
						"DESCRIPTION='"+ o.getDescription() +"'" +
				" WHERE item_id="+ o.getId();

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.execute(query);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
			System.out.println(query);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
	
	

	public static void newOffer(OfferBean o){

		//preparing some objects for connection 
		Statement stmt = null; 

		String query = "INSERT INTO items (name,price, country, region, quantity, description) VALUES  " +
				" ('"+ o.getName() +"', "+ o.getPrice() +", "+ o.getCountry() +", '"+ o.getRegion() +"', "+ o.getQuantity() +", " +
				"'"+ o.getDescription() +"')";

		try 
		{
			//connect to DB 
			currentCon = ConnectionManager.getConnection();
			stmt=currentCon.createStatement();
			stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);	   
			
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()){
			    o.setId(rs.getInt(1));
			}
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
			System.out.println(query);
		} 

		//some exception handling
		finally 
		{
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}
}
